package com.cpi.cornpp.grinddistributionversion.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.cpi.cornpp.common.exception.CornPPException;
import com.cpi.cornpp.grinddistributionversion.dto.GrindDistributionPrioritiesVersionDTO;
import com.cpi.cornpp.grinddistributionversion.dto.GrindDistributionSettingsVersionDTO;
import com.cpi.cornpp.grinddistributionversion.exception.GrindDistributionVersionException;
import com.cs.common.pool.DBDAO;
import com.cs.common.pool.exception.PoolException;

/**
 * Grind Priority Distribution Version Data Access Object
 * @author Edgar Joao
 *
 */
public class GrindDistributionVersionDAO extends DBDAO{
	private static final String SELECT_GRIND_SETTINGS_LIST_BY_VERSION_ID = "SELECT plants.month_period_version_id, plants.plant_version_id, ct.corn_type_id, unloads.unload_setting_version_id, pl.short_name, unloads.unload_amount, unloads.fixed FROM	vw_v_plants_versions plants inner join corns_types ct on 1 = 1 left join plants pl on pl.plant_id = plants.plant_id left join unloads_settings_versions unloads on plants.plant_version_id = unloads.plant_version_id and unloads.corn_type_id = ct.corn_type_id WHERE plants.version_id = ?  ?monthPeriodVersionId?  order by short_name, plant_version_id, ct.corn_type_id";
	private static final String SELECT_GRIND_PRIORITIES_LIST_BY_VERSION_ID = "SELECT plants.month_period_version_id, plants.plant_version_id, ct.corn_type_id, grinds.grind_distribution_priority_version_id, grinds.priority FROM vw_v_plants_versions plants inner join corns_types ct on 1 = 1 left join plants pl ON pl.plant_id = plants.plant_id left join grinds_distributions_priorities_versions grinds ON plants.plant_version_id = grinds.plant_version_id and grinds.corn_type_id = ct.corn_type_id WHERE plants.version_id = ?  ?monthPeriodVersionId?  order by short_name, plant_version_id, ct.corn_type_id";
	private static final String SQL_INSERT_PRIORITIES = "INSERT INTO grinds_distributions_priorities_versions (priority, plant_version_id, corn_type_id) values (?, ?, ?);";
	private static final String SQL_UPDATE_PRIORITIES = "UPDATE grinds_distributions_priorities_versions SET priority = ? where grind_distribution_priority_version_id = ? ";
	private static final String SQL_INSERT_SETTINGS = "INSERT INTO unloads_settings_versions (unload_amount, fixed, plant_version_id, corn_type_id) VALUES (?,?,?,?);";
	private static final String SQL_UPDATE_SETTINGS = "UPDATE unloads_settings_versions SET unload_amount = ?, fixed = ? WHERE unload_setting_version_id = ? ";
	private static final String SQL_UPDATE_UNLOAD_AMOUNT_IN_PLANTS_VERSIONS = "UPDATE plants_versions SET unload_amount = ? WHERE plant_version_id = ?";
	private static final String SQL_EXIST_UNLOAD_PLANTS_VERSIONS = "SELECT * FROM plants_versions WHERE month_period_version_id = ? AND plant_version_id = ?";
	/**
	 * Get List of grind distribution by version
	 * @param versionId
	 * @return
	 * @throws GrindDistributionVersionException
	 */
	public List getGrindSettingList(int versionId, int periodId) throws GrindDistributionVersionException{
		List grindSettingsList = null;
		GrindDistributionSettingsVersionDTO dto = null;		
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;
		String sql = null;
		try{
			grindSettingsList = new ArrayList(0);
			sql = SELECT_GRIND_SETTINGS_LIST_BY_VERSION_ID;
			if( periodId> 0 ){
				sql = sql.replaceAll("\\?monthPeriodVersionId\\?", " and plants.month_period_version_id = ? ");
			}else{
				sql = sql.replaceAll("\\?monthPeriodVersionId\\?", "");
			}
			preparedStatement = prepareSQL(sql, dsName);
			preparedStatement.setInt(1, versionId);
			if( periodId> 0 ){
				preparedStatement.setInt(2, periodId);
			}
			rs = executeQuery(preparedStatement);
			while(rs.next()){
				dto =  new GrindDistributionSettingsVersionDTO();
				dto.setMonthPeriodVersionId(new Integer(rs.getInt(1)));
				dto.setPlantVersionId(new Integer(rs.getInt(2)));
				dto.setCornTypeId(new Integer(rs.getInt(3)));
				dto.setUnloadSettingVersionId(new Integer(4));
				dto.setShortName(rs.getString(5));
				dto.setUnloadAmount(rs.getDouble(6));
				dto.setFixed(new Integer(rs.getInt(7)));
				grindSettingsList.add(dto);				
			}
		}catch (PoolException poolException){
			GrindDistributionVersionException grindDistributionVersionException = null;
			poolException.printStackTrace(System.out);
			grindDistributionVersionException = new GrindDistributionVersionException(poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_LISTS);
			throw grindDistributionVersionException;	
		} catch (SQLException sqlException) {
			GrindDistributionVersionException grindDistributionVersionException = null;
			sqlException.printStackTrace(System.out);
			grindDistributionVersionException = new GrindDistributionVersionException(sqlException, CornPPException.LAYER_DAO, CornPPException.ACTION_LISTS);
			throw grindDistributionVersionException;
		} finally {			
			try {				
				closeConnection(preparedStatement.getConnection());				
			} catch (SQLException sqle) {
				sqle.printStackTrace(System.out);
			}
		}
		return grindSettingsList;
	}
	/**
	 * Get List of grind priorities by version
	 * @param versionId
	 * @return
	 * @throws GrindDistributionVersionException
	 */
	public List getGrindPrioritiesList(int versionId, int periodId) throws GrindDistributionVersionException{
		List grindPrioritiesList = null;		
		GrindDistributionPrioritiesVersionDTO dto = null;
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;	
		String sql = null;
		try{
			grindPrioritiesList = new ArrayList(0);
			sql = SELECT_GRIND_PRIORITIES_LIST_BY_VERSION_ID;
			if( periodId> 0 ){
				sql = sql.replaceAll("\\?monthPeriodVersionId\\?", " and plants.month_period_version_id = ? ");
			}else{
				sql = sql.replaceAll("\\?monthPeriodVersionId\\?", "");
			}
			preparedStatement = prepareSQL(sql, dsName);
			if( periodId> 0 ){
				preparedStatement.setInt(2, periodId);
			}
			preparedStatement.setInt(1, versionId);	
			rs = executeQuery(preparedStatement);
			while(rs.next()){				
				dto = new GrindDistributionPrioritiesVersionDTO();
                dto.setMonthPeriodVersionId(new Integer(rs.getInt(1)));
                dto.setPlantVersionId(new Integer(rs.getInt(2)));
                dto.setCornTypeId(new Integer(rs.getInt(3)));
                dto.setGrindDistributionPriorityVersionId(new Integer(rs.getInt(4)));
                dto.setPriority(new Integer(rs.getInt(5)));
				grindPrioritiesList.add(dto);				
			}
		}catch (PoolException poolException){
			GrindDistributionVersionException grindDistributionVersionException = null;
			poolException.printStackTrace(System.out);
			grindDistributionVersionException = new GrindDistributionVersionException(poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_LISTS);
			throw grindDistributionVersionException;	
		} catch (SQLException sqlException) {
			GrindDistributionVersionException grindDistributionVersionException = null;
			sqlException.printStackTrace(System.out);
			grindDistributionVersionException = new GrindDistributionVersionException(sqlException, CornPPException.LAYER_DAO, CornPPException.ACTION_LISTS);
			throw grindDistributionVersionException;
		} finally {			
			try {				
				closeConnection(preparedStatement.getConnection());				
			} catch (SQLException sqle) {
				sqle.printStackTrace(System.out);
			}
		}
		return grindPrioritiesList;
	}
	
	/**
	 * Metodo que actualiza la Descarga
	 */
	public void updateUnload(int plantVersionId, double sumAmount) throws GrindDistributionVersionException{
		PreparedStatement preparedStatement = null;		
		try{       			
			preparedStatement = prepareSQL(SQL_UPDATE_UNLOAD_AMOUNT_IN_PLANTS_VERSIONS, dsName);
			preparedStatement.setDouble(1, sumAmount);		
			preparedStatement.setInt(2, plantVersionId);
			executeInsert(preparedStatement);						
		}catch (PoolException poolException){
			GrindDistributionVersionException grindDistributionVersionException = null;
			poolException.printStackTrace(System.out);
			grindDistributionVersionException = new GrindDistributionVersionException(poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_LISTS);
			throw grindDistributionVersionException;	
		} catch (SQLException sqlException) {
			GrindDistributionVersionException grindDistributionVersionException = null;
			sqlException.printStackTrace(System.out);
			grindDistributionVersionException = new GrindDistributionVersionException(sqlException, CornPPException.LAYER_DAO, CornPPException.ACTION_LISTS);
			throw grindDistributionVersionException;
		} finally {			
			try {				
				closeConnection(preparedStatement.getConnection());				
			} catch (SQLException sqle) {
				sqle.printStackTrace(System.out);
			}
		}
		
	}
	
	public boolean existPlantVersion(int plantVersionId, int monthPeriodVersionId)throws GrindDistributionVersionException {
		PreparedStatement preparedStatement = null;				
		boolean result = false;		
		try{
			preparedStatement =  prepareSQL(SQL_EXIST_UNLOAD_PLANTS_VERSIONS, dsName);	
			preparedStatement.setInt(1, monthPeriodVersionId);
			preparedStatement.setInt(2, plantVersionId);
			ResultSet rst = executeQuery(preparedStatement);
			if (rst.next()) {				
				result=true;							
			}else{
				result=false;
			}		
		}catch (PoolException poolException){
			GrindDistributionVersionException grindDistributionVersionException = null;
			poolException.printStackTrace(System.out);
			grindDistributionVersionException = new GrindDistributionVersionException(poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_LISTS);
			throw grindDistributionVersionException;	
		} catch (SQLException sqlException) {
			GrindDistributionVersionException grindDistributionVersionException = null;
			sqlException.printStackTrace(System.out);
			grindDistributionVersionException = new GrindDistributionVersionException(sqlException, CornPPException.LAYER_DAO, CornPPException.ACTION_LISTS);
			throw grindDistributionVersionException;
		} finally {			
			try {				
				closeConnection(preparedStatement.getConnection());				
			} catch (SQLException sqle) {
				sqle.printStackTrace(System.out);
			}
		}
		return result;
	}
	
	public void save(GrindDistributionPrioritiesVersionDTO grindDistributionPrioritiesVersionDTO, GrindDistributionSettingsVersionDTO grindDistributionSettingsVersionDTO) throws GrindDistributionVersionException{
		PreparedStatement preparedStatement = null;
		String sqlPriorities = null;
		String sqlSettings = null;
		try{
			if( grindDistributionPrioritiesVersionDTO.getGrindDistributionPriorityVersionId().intValue() != 0 ){
				sqlPriorities = SQL_UPDATE_PRIORITIES;
			}else{
				sqlPriorities = SQL_INSERT_PRIORITIES;
			}
			preparedStatement = prepareSQL(sqlPriorities, dsName);
			preparedStatement.setInt(1, grindDistributionPrioritiesVersionDTO.getPriority().intValue());
			if( grindDistributionPrioritiesVersionDTO.getGrindDistributionPriorityVersionId().intValue() != 0 ){
				preparedStatement.setInt(2, grindDistributionPrioritiesVersionDTO.getGrindDistributionPriorityVersionId().intValue() );
			}else{
				preparedStatement.setInt(2, grindDistributionPrioritiesVersionDTO.getCornTypeId().intValue() );
				preparedStatement.setInt(3, grindDistributionPrioritiesVersionDTO.getPriority().intValue());
			}
			executeInsert(preparedStatement);
			closeConnection(preparedStatement.getConnection());
			
			if( grindDistributionSettingsVersionDTO.getUnloadSettingVersionId().intValue() != 0 ){
				sqlSettings = SQL_UPDATE_SETTINGS;
			}else{
				sqlSettings = SQL_INSERT_SETTINGS;
			}
			preparedStatement = prepareSQL(sqlSettings, dsName);
			preparedStatement.setDouble(1, grindDistributionSettingsVersionDTO.getUnloadAmount());
			preparedStatement.setInt(2, grindDistributionSettingsVersionDTO.getFixed().intValue());
			if( grindDistributionSettingsVersionDTO.getUnloadSettingVersionId().intValue() != 0 ){
				preparedStatement.setInt(3, grindDistributionSettingsVersionDTO.getUnloadSettingVersionId().intValue());
			}else{
				preparedStatement.setInt(3, grindDistributionSettingsVersionDTO.getMonthPeriodVersionId().intValue());
				preparedStatement.setInt(4, grindDistributionSettingsVersionDTO.getCornTypeId().intValue());
			}
			executeInsert(preparedStatement);
		}catch (PoolException poolException){
			GrindDistributionVersionException grindDistributionVersionException = null;
			poolException.printStackTrace(System.out);
			grindDistributionVersionException = new GrindDistributionVersionException(poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_LISTS);
			throw grindDistributionVersionException;	
		} catch (SQLException sqlException) {
			GrindDistributionVersionException grindDistributionVersionException = null;
			sqlException.printStackTrace(System.out);
			grindDistributionVersionException = new GrindDistributionVersionException(sqlException, CornPPException.LAYER_DAO, CornPPException.ACTION_LISTS);
			throw grindDistributionVersionException;
		} finally {			
			try {				
				closeConnection(preparedStatement.getConnection());				
			} catch (SQLException sqle) {
				sqle.printStackTrace(System.out);
			}
		}
	}
}